<?php

namespace App\Http\Controllers\Home;

use App\Model\UserSearchRecords;
use App\Model\WeChatFromId;
use App\Model\WineProduct;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class SearchController extends BaseController
{
    private $query;
    /*
     * 产品类别 wine_product.category
     * 产区：国家+大区 +子产区（例：法国、波尔多、梅多克）sys_country,sys_g_regions,sys_subdivision
     * 生产年份  wine_product.vintage
     * 消费场景  wine_product_scene.name
     * 菜系     wine_cuisines.name
     * 产品档次  wine_gradings.name
     * 品种     wine_product_deployment.name
     * 产品风格  wine_tastes.name
     */

    public function search(Request $request)
    {
        $items = [];
        $keyword = $request->input('keyword');

        if ($formid = $request->input('formid')){
            WeChatFromId::createFromForm($formid,$this->loginUser()->weixinId);
        }

        if ($keyword) {
            $pageSize = 10;

            $ids = UserSearchRecords::getSearchResult($keyword);

            if (!$ids) $ids = $this->getProductIds($keyword);

            $ids = $this->getProductIds($keyword);

            $items = WineProduct::whereIn('id',$ids)->whereNull('deleted_at')
                ->where('online','=',"yes")
                ->orderBy('weight','desc')
                ->orderBy('created_at','desc')
                ->with(['userHasDrink','userWantDrink',"wineGrading","scenes",'drinkTasteKeywords'])
                ->select('id','name','chname','chateauName','vintage','banner','price','promotion_price',
                    'show_cuisine','show_scenes','letDrink_recom','grading_id')
                ->paginate($pageSize);
            if ($ids) {
                //保存搜索结果
                UserSearchRecords::saveRecord($this->loginUserId(),$keyword,json_encode($ids));
            }
        }

        $like =  UserSearchRecords::getLike($this->loginUserId());

        $hot =UserSearchRecords::getHotKeywords();

        return $this->success(['result'=>$items,'like'=>$like,'hot'=>$hot]);
    }

    public function getProductIds($keyword)
    {
        //DB::enableQueryLog();

        $row[1] = $this->processName($keyword);
        $row[2] = $this->processCategory($keyword);
        $row[3] = $this->processCountry($keyword);
        $row[4] = $this->processGRegion($keyword);
        $row[5] = $this->processRegion($keyword);

        $row[6] = $this->processVintage($keyword);
        $row[7] = $this->processScene($keyword);
        $row[8] = $this->processCuisine($keyword);
        $row[9] = $this->processGrading($keyword);
        $row[10] = $this->processDeployment($keyword);

        $row[11] = $this->processTaste($keyword);
        $row[12] = $this->processDrinkKeywords($keyword);

        $ids = [];
        foreach ($row as $v){
            $ids = array_merge($ids,$v->toArray());
        }

        $ids = array_unique($ids);

        //$ids = array_slice($ids,$pageSize*($page-1),$pageSize);

        //dd($ids,DB::getQueryLog());

        return $ids;
    }

    //1.产品名称
    public function processName($keyword)
    {
        return DB::table('wine_product')->whereRaw('`name` like ? or `chname` like ?',["%".$keyword."%","%".$keyword."%"])->pluck('id');
    }

    //2.产品类别
    public function processCategory($keyword)
    {
        return DB::table('wine_product')->where('category','like','%'.$keyword.'%')->pluck('id');
    }
    //3.国家
    public function processCountry($keyword)
    {
        return DB::table('wine_product')->whereRaw('`country_id` in (select id from `sys_country` where 
        country like "%'.$keyword.'%" or chCountry like "%'.$keyword.'%")')->pluck('id');
    }
    //4.大区
    public function processGRegion($keyword)
    {
        return DB::table('wine_product')->whereRaw('`g_region_id` in (select id from `sys_g_regions` where 
        G_Region like "%'.$keyword.'%" or CH_G_Region like "%'.$keyword.'%")')->pluck('id');
    }
    //5.产区
    public function processRegion($keyword)
    {
        return DB::table('wine_product')->whereRaw('`region_id` in (select id from `sys_region` where 
        region like "%'.$keyword.'%" or chRegion like "%'.$keyword.'%")')->pluck('id');
    }
    //6.生产年份
    public function processVintage($keyword)
    {
        return DB::table('wine_product')->where('vintage','=',$keyword)->pluck('id');

    }
    //7.消费场景
    public function processScene($keyword)
    {
        $ids = DB::table('wine_product_scene')->where('name','like','%'.$keyword.'%')->pluck('id');
        $sql = 0;
        if (count($ids)>0) {
            $ids = implode(',', $ids->toArray());
            $sql = 'scene_one_id in ('.$ids.') or scene_two_id in ('.$ids.') or scene_three_id in ('.$ids.')';
        }
        return DB::table('wine_product_scene_relation')->whereRaw($sql)->pluck('product_id');
    }
    //8.菜系
    public function processCuisine($keyword)
    {
        return DB::table('wine_product_cuisine_relations')->whereRaw('`wine_cuisines_id` in (select id from `wine_cuisines` where 
        name like "%'.$keyword.'%")')->pluck('product_id');
    }
    //9.产品档次
    public function processGrading($keyword)
    {
        return DB::table('wine_product')->whereRaw('`grading_id` in (select id from `wine_gradings` where 
        name like "%'.$keyword.'%")')->pluck('id');
    }
    //10.品种
    public function processDeployment($keyword)
    {
        return DB::table('wine_product_deployment')->where('name','like','%'.$keyword.'%')->pluck('product_id');
    }
    //11.产品风格
    public function processTaste($keyword)
    {
        return DB::table('wine_product')->whereRaw('`taste_id` in (select id from `wine_tastes` where 
        name like "%'.$keyword.'%")')->pluck('id');
    }

    //12.味道关键词
    public function processDrinkKeywords($keyword)
    {
        return DB::table('wine_drink_keywords')->where('keyword','like','%'.$keyword.'%')->pluck('product_id');
    }
}
